package org.bigdata.framework.admin.web.controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.bigdata.framework.core.model.Classes;
import org.bigdata.framework.core.model.DataProperty;
import org.bigdata.framework.core.model.Dict;
import org.bigdata.framework.core.model.DictItem;
import org.bigdata.framework.core.model.ObjectProperty;
import org.bigdata.framework.core.model.User;
import org.bigdata.framework.core.protocol.IBaseService;
import org.bigdata.framework.core.protocol.ICmsService;
import org.bigdata.framework.core.protocol.IDynamicService;
import org.bigdata.framework.core.protocol.IPublicService;
import org.bigdata.framework.core.protocol.ISearchService;
import org.bigdata.framework.core.protocol.IWordService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.xxl.conf.core.XxlConfClient;

@Controller
public class RelevanceListController extends BaseContoroller{
	@Autowired
	private IPublicService publicService;
	@Autowired
	private IBaseService baseService;
	@Autowired
	private IWordService iWordService;
	@Autowired
	private IDynamicService dynamicService ;
	@Autowired
	private ISearchService searchService;
	@Autowired 
	private ICmsService cmsService;
	// 数据库类型
	private static String jdbcUrl = XxlConfClient.get("dal.jdbc.url", "");
	// 数据库类型
	private static String jdbcUserName = XxlConfClient.get("dal.jdbc.username", "");
	// 数据库类型
	private static String jdbcPassWord = XxlConfClient.get("dal.jdbc.password", "");
	 Connection con = null;
	 PreparedStatement ps = null;

	 ResultSet rs = null;


	public Connection  getConnection() {
		
			try {
				Class.forName("com.mysql.jdbc.Driver");
				con = DriverManager.getConnection(jdbcUrl, jdbcUserName,jdbcPassWord);
				//con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/dal_khzl", "root","root");
			} catch (Exception e) {
				e.printStackTrace();
			} 
			return con;
	}

	public  void close(ResultSet rs,PreparedStatement ps,Connection con) {
		if (rs != null)
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		if (ps != null)
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		if (con != null)
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}
	//查询列表sql
	public List<HashMap<String, Object>> queryKnowledgeDataList(String startTime,String endTime,String page,String shengfen,String danwei,String shouji,String xingming,String kehujibie,String kehuleibie,String lastContactstartTime,String lastContactendTime,int userid,String userIds) {
		List<HashMap<String, Object>> r = new ArrayList<HashMap<String, Object>>();
		LinkedHashMap<String, Object> mp = null;
		this.getConnection();
		 String sql = "select t2.id , t2.xing_ming , t2.dan_wei , t2.sheng_fen , t2.shou_ji , t2.ke_hu_lei_bie , t2.ke_hu_ji_bie , t.op_create_time,t3.lian_xi_qing_kuang ,t3.xia_ci_lian_xi_shi_jian ,t3.lian_xi_nei_rong "
				 	+" from  hymax_ke_hu_zi_liao  t2 ,hymax_lian_xi_qing_kuang t3,(select t1.source_data_id  source_id, max(t1.target_data_id) target_id,max(t1.create_time ) op_create_time "
				 	+" from hymax_ke_hu_zi_liao_op_lian_xi_xin_xi  t1 "
				 	+" group by  t1.source_data_id order by t1.create_time) t "
				 	+" where t2.id = t.source_id and t3.id = t.target_id ";
				 if(null != shengfen && !"".equals(shengfen)){
					 sql += " and t2.sheng_fen = '"+shengfen+"' ";
				 }
				 if(null != danwei && !"".equals(danwei)){
					 sql += " and t2.dan_wei LIKE '%"+danwei+"%' ";
				 }
				 if(null != shouji && !"".equals(shouji)){
					 sql += " and t2.shou_ji LIKE '%"+shouji+"%' ";
				 }
				 if(null != xingming && !"".equals(xingming)){
					 sql += " and t2.xing_ming LIKE '%"+xingming+"%' ";
				 }
				 if(null != kehujibie && !"".equals(kehujibie)){
					 sql += " and t2.ke_hu_ji_bie = '"+kehujibie+"' ";
				 }
				 if(null != kehuleibie && !"".equals(kehuleibie)){
					 sql += " and t2.ke_hu_lei_bie = '"+kehuleibie+"' ";
				 }
				 if(null != userIds && !"".equals(userIds)){
					 sql += " and t2.create_user_id = '"+userIds+"' ";
				 }
				 if(null != startTime && !"".equals(startTime) && null != endTime && !"".equals(endTime) ){
					 sql += " and (t.op_create_time>='"+startTime+"' and t.op_create_time<'"+endTime+"') ";
				 }
				 if(null != lastContactstartTime && !"".equals(lastContactstartTime) && null != lastContactendTime && !"".equals(lastContactendTime) ){
					 sql += " and (t3.xia_ci_lian_xi_shi_jian>='"+lastContactstartTime+"' and t3.xia_ci_lian_xi_shi_jian<'"+lastContactendTime+"') ";
				 }
				 if(userid != 1){
					 sql += " and t2.create_user_id = '"+userid+"' ";
				 }
				 sql +=" order by t.op_create_time desc  LIMIT ?, 15";
		try {
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, (Integer.parseInt(page)-1)*15);
			rs=ps.executeQuery();
			while(rs.next()){
				mp = new LinkedHashMap<String, Object>();
				mp.put("id", rs.getString("id"));
				mp.put("xingMing", rs.getString("xing_ming"));
				mp.put("lianXiQingQuang", rs.getString("lian_xi_qing_kuang"));
				mp.put("xiaCiLianXiShiJian", rs.getString("xia_ci_lian_xi_shi_jian"));
				mp.put("lianXiNeiRong", rs.getString("lian_xi_nei_rong"));
				mp.put("shengFen", rs.getString("sheng_fen"));
				mp.put("danWei", rs.getString("dan_wei"));
				mp.put("shouJi", rs.getString("shou_ji"));
				mp.put("keHuJiBie", rs.getString("ke_hu_ji_bie"));
				mp.put("keHuLeiBie", rs.getString("ke_hu_lei_bie"));
				mp.put("opCreateTime", rs.getString("op_create_time"));
				r.add(mp);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{  
           // 关闭数据库连接  
			this.close(rs,ps,con);  
       } 
		  return r;
	}
	//查询总数sql
	public int queryKnowledgeDataCount(String startTime,String endTime,String shengfen,String danwei,String shouji,String xingming,String kehujibie,String kehuleibie,String lastContactstartTime,String lastContactendTime,int userid,String userIds) {
		this.getConnection();
		String sql = "select COUNT(DISTINCT t2.id) much "
			 	+" from  hymax_ke_hu_zi_liao  t2 ,hymax_lian_xi_qing_kuang t3,(select t1.source_data_id  source_id,t1.target_data_id target_id,max(t1.create_time ) op_create_time "
			 	+" from hymax_ke_hu_zi_liao_op_lian_xi_xin_xi  t1 "
			 	+" group by  t1.source_data_id) t "
			 	+" where t2.id = t.source_id and t3.id = t.target_id ";
			 if(null != shengfen && !"".equals(shengfen)){
				 sql += " and t2.sheng_fen = '"+shengfen+"' ";
			 }
			 if(null != danwei && !"".equals(danwei)){
				 sql += " and t2.dan_wei LIKE '%"+danwei+"%' ";
			 }
			 if(null != shouji && !"".equals(shouji)){
				 sql += " and t2.shou_ji LIKE '%"+shouji+"%' ";
			 }
			 if(null != xingming && !"".equals(xingming)){
				 sql += " and t2.xing_ming LIKE '%"+xingming+"%' ";
			 }
			 if(null != kehujibie && !"".equals(kehujibie)){
				 sql += " and t2.ke_hu_ji_bie = '"+kehujibie+"' ";
			 }
			 if(null != kehuleibie && !"".equals(kehuleibie)){
				 sql += " and t2.ke_hu_lei_bie = '"+kehuleibie+"' ";
			 }
			 if(null != userIds && !"".equals(userIds)){
				 sql += " and t2.create_user_id = '"+userIds+"' ";
			 }
			 if(null != startTime && !"".equals(startTime) && null != endTime && !"".equals(endTime) ){
				 sql += " and (t.op_create_time>='"+startTime+"' and t.op_create_time<'"+endTime+"') ";
			 }
			 if(null != lastContactstartTime && !"".equals(lastContactstartTime) && null != lastContactendTime && !"".equals(lastContactendTime) ){
				 sql += " and (t3.xia_ci_lian_xi_shi_jian>='"+lastContactstartTime+"' and t3.xia_ci_lian_xi_shi_jian<'"+lastContactendTime+"') ";
			 }
			 if(userid != 1){
				 sql += " and t2.create_user_id = '"+userid+"' ";
			 }
			 sql +=" order by t.op_create_time desc ";
		int count = 0;
		try {
			PreparedStatement ps = con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				count = rs.getInt(1);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{  
			// 关闭数据库连接  
			this.close(rs,ps,con);  
		} 
		return count;
	}
	
	//关联页面
	@RequestMapping("show-data-relevance-list.html")
	public ModelAndView toKnowledgeEditDataClassRelevanceUploading(HttpServletRequest request,HttpServletResponse response) {
		List<DictItem> kehujibie_dictItem = publicService.getDictItem(publicService.findDict(14));
		List<DictItem> kehuleibie_dictItem = publicService.getDictItem(publicService.findDict(15));
		List<DictItem> shengfen_dictItem = publicService.getDictItem(publicService.findDict(19));
		Integer userid =(Integer) request.getSession().getAttribute("userid");
		List<User> userList = publicService.getUser();
		ModelAndView view = new ModelAndView();
		view.addObject("kehujibie_dictItem", kehujibie_dictItem);
		view.addObject("kehuleibie_dictItem", kehuleibie_dictItem);
		view.addObject("shengfen_dictItem", shengfen_dictItem);
		view.addObject("userList", userList);
		view.addObject("userid", userid);
		view.setViewName("/modules/repository/show_data_relevance_list");
		return view;
	}
	
	//查询列表
	@RequestMapping("searchRelevanceList")
	public void searchRelevanceList(HttpServletRequest request,HttpServletResponse response) throws Exception {
		//保存人
		Integer userid =(Integer) request.getSession().getAttribute("userid");
		String page = request.getParameter("page");
		String startTime = request.getParameter("startTime");
		String endTime = request.getParameter("endTime");
		String shengfen = request.getParameter("shengfen");
		String danwei = request.getParameter("danwei");
		String shouji = request.getParameter("shouji");
		String xingming = request.getParameter("xingming");
		String kehujibie = request.getParameter("kehujibie");
		String kehuleibie = request.getParameter("kehuleibie");
		String lastContactstartTime = request.getParameter("lastContactstartTime");
		String lastContactendTime = request.getParameter("lastContactendTime");
		String userIds = request.getParameter("user");
		List<HashMap<String, Object>> r = this.queryKnowledgeDataList(startTime, endTime, page, shengfen, danwei, shouji, xingming, kehujibie, kehuleibie,lastContactstartTime,lastContactendTime,userid,userIds);
		int count = this.queryKnowledgeDataCount(startTime, endTime, shengfen, danwei, shouji, xingming, kehujibie, kehuleibie,lastContactstartTime,lastContactendTime,userid,userIds);
		HashMap<String, Object> data = new HashMap<String, Object>();
		data.put("data", r);
		data.put("count", count);
		data.put("page", (int)Math.ceil(Double.valueOf(count)/15));
		String result = formatToJson(data);
		super.pringWriterToPage(result.toString(), "application/json", response);
	
	}
}
